﻿Imports System.IO
Imports System.Text
Imports NPOI.HSSF.UserModel
Imports NPOI.HSSF.Util
Imports NPOI.SS.UserModel

Public Class CExcelProcess
    Public Shared Property CellColumns As Integer
    ''' <summary>
    ''' 为ListView绑定DataTable数据项
    ''' </summary>
    ''' <param name="dt">DataTable</param>
    ''' <param name="lv">ListView控件</param>
    Public Shared Sub DataTableToListview(ByVal dt As DataTable, ByVal lv As ListView)
        If dt IsNot Nothing Then
            lv.View = View.Details
            lv.GridLines = True '显示网格线
            lv.Items.Clear() '所有的项
            lv.Columns.Clear() '标题
            For i As Integer = 0 To dt.Columns.Count - 1
                lv.Columns.Add(dt.Columns(i).Caption.ToString()) '增加标题
            Next i
            For i As Integer = 0 To dt.Rows.Count - 1
                Dim lvi As New ListViewItem(dt.Rows(i)(0).ToString())
                For j As Integer = 0 To dt.Columns.Count - 1
                    ' lvi.ImageIndex = 0;
                    lvi.SubItems.Add(dt.Rows(i)(j).ToString())
                Next j
                lv.Items.Add(lvi)
            Next i
            lv.AutoResizeColumns(ColumnHeaderAutoResizeStyle.HeaderSize) '调整列的宽度
        End If
    End Sub
    ''' <summary>
    ''' 将DataTable填充到Excel中
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <returns></returns>
    Public Shared Function DataTableToExcel(dt As DataTable, ExcelName As String) As Boolean
        Dim result As Boolean = False
        Dim workbook As IWorkbook = Nothing
        Dim fs As FileStream = Nothing
        Dim row As IRow = Nothing
        Dim sheet As ISheet = Nothing
        Dim cell As ICell = Nothing
#If Not DEBUG Then
                Try
#End If
        If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then
            workbook = New HSSFWorkbook()
            sheet = workbook.CreateSheet("Sheet0") '创建一个名称为Sheet0的表
            Dim rowCount As Integer = dt.Rows.Count '行数
            Dim columnCount As Integer = dt.Columns.Count '列数
            CellColumns = columnCount
            '设置列头  
            row = sheet.CreateRow(0) 'excel第一行设为列头
            Dim CollmnStyle = SetColumCellStyle(workbook)
            For c As Integer = 0 To columnCount - 1
                cell = row.CreateCell(c)
                cell.SetCellValue(dt.Columns(c).ColumnName)
                sheet.AutoSizeColumn(c)
                cell.CellStyle = CollmnStyle
            Next c
            '设置每行每列的单元格,  
            Dim CellStyle = SetCellStyle(workbook)
            For i As Integer = 0 To rowCount - 1
                row = sheet.CreateRow(i + 1)
                For j As Integer = 0 To columnCount - 1
                    cell = row.CreateCell(j) 'excel第二行开始写入数据
                    cell.SetCellValue(dt.Rows(i)(j).ToString())
                    'Debug.Print(dt.Rows(i)(j).ToString())
                    cell.CellStyle = CellStyle
                Next j
            Next i
            SetColumWidth(sheet)                '设置表格宽度根据文本自适应
            sheet.CreateFreezePane(0, 1, 0, 1)  '首行冻结  (1,0,1,0)  首列冻结
            fs = File.OpenWrite(ExcelName)
            Using fs
                workbook.Write(fs) '向打开的这个xls文件中写入数据
                result = True
            End Using
        End If
        Return result
#If Not DEBUG Then
                Catch ex As Exception
                    If fs IsNot Nothing Then
                        fs.Close()
                    End If
                    Return False
                End Try
#End If
    End Function
    ''' <summary>
    ''' 根据文本内容自动居中
    ''' </summary>
    ''' <param name="sheet"></param>
    Private Shared Sub SetColumWidth(sheet As ISheet)
        For columnNum As Integer = 0 To CellColumns
            sheet.AutoSizeColumn(columnNum) '先来个常规自适应
            Dim columnWidth As Integer = sheet.GetColumnWidth(columnNum) / 256
            For rowNum As Integer = 1 To sheet.LastRowNum
                Dim currentRow As IRow
                '当前行未被使用过
                If sheet.GetRow(rowNum) Is Nothing Then
                    currentRow = sheet.CreateRow(rowNum)
                Else
                    currentRow = sheet.GetRow(rowNum)
                End If
                If currentRow.GetCell(columnNum) IsNot Nothing Then
                    Dim currentCell As ICell = currentRow.GetCell(columnNum)
                    Dim length As Integer = Encoding.Default.GetBytes(DirectCast(currentCell, Object).ToString()).Length
                    If columnWidth < length Then columnWidth = length
                End If
            Next rowNum
            If columnWidth > 255 Then
                sheet.SetColumnWidth(columnNum, 255 * 256)
            Else
                sheet.SetColumnWidth(columnNum, columnWidth * 300) '一般是*256，给加宽一丢丢
            End If
        Next columnNum
    End Sub
    ''' <summary>
    ''' 设置列标题的样式
    ''' </summary>
    ''' <param name="workbook"></param>
    ''' <returns></returns>
    Public Shared Function SetColumCellStyle(workbook As IWorkbook) As ICellStyle
        Dim style = workbook.CreateCellStyle()
        style.FillBackgroundColor = HSSFColor.Gold.Index
        style.FillForegroundColor = HSSFColor.Black.Index
        style.Alignment = HorizontalAlignment.Center
        style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index
        Dim Font = workbook.CreateFont()
        Font.FontHeightInPoints = 16
        'Font.Boldweight = FontBoldWeight.Bold
        Font.FontName = "微软雅黑"
        style.SetFont(Font)
        Return style
    End Function
    ''' <summary>
    ''' 设置单元格的样式
    ''' </summary>
    ''' <param name="workbook"></param>
    ''' <returns></returns>
    Public Shared Function SetCellStyle(workbook As IWorkbook) As ICellStyle
        Dim styleCommonLeft = workbook.CreateCellStyle()
        styleCommonLeft.Alignment = HorizontalAlignment.Center '水平居中
        styleCommonLeft.VerticalAlignment = VerticalAlignment.Center '垂直居中
        styleCommonLeft.WrapText = True
        Dim fontLeft = workbook.CreateFont()
        fontLeft.FontHeightInPoints = 10
        'fontLeft.Boldweight = FontBoldWeight.Normal
        fontLeft.FontName = "微软雅黑"
        styleCommonLeft.SetFont(fontLeft)
        Return styleCommonLeft
    End Function
End Class
